package com.hnkywl.jip.dao;

import com.hnkywl.jip.utils.CompanyData;
import net.ruixin.orm.hibernate.Page;
import net.ruixin.orm.hibernate.SimpleHibernateTemplate;
import org.hibernate.SQLQuery;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * Created with IntelliJ IDEA.
 * User: jerry
 * Date: 13-3-1
 * Time: 上午10:08
 * To change this template use File | Settings | File Templates.
 */
@Repository
public class CompanyDao extends SimpleHibernateTemplate{
    public List<Object[]> searchAllCompany(){
        String sql="select compid,compname=dbo.gf_compid_fullname(a.compid),shortname=dbo.gf_compid_shortname(a.compid) from dbo.t_sys_comp a where a.status=1 order by a.rank";
        SQLQuery query = createSQLQuery(sql);
        List<Object[]> list=query.list();
        return list;
    }

    public Page<Object[]> searchCompanyMapping(Integer companyId,Integer start,Integer limit){
        String sql="select compid,compname=dbo.gf_compid_fullname(a.compid),b.code" +
                " from dbo.t_sys_comp a left join t_jiuqi_company_mapping b on a.compid=b.company2_id and b.company1_id=:companyId" +
                " where a.status=1 and a.compid!=:companyId";

        String sqlCount = "select count(1)  from dbo.t_sys_comp a left join t_jiuqi_company_mapping b on a.compid=b.company2_id and b.company1_id=:companyId" +
        " where a.status=1 and a.compid!=:companyId";
        SQLQuery query = createSQLQuery(sqlCount);
        query.setInteger("companyId",companyId);
        Integer total = (Integer) query.uniqueResult();

        query = createSQLQuery(sql);
        query.setInteger("companyId",companyId);
        List<Object[]> result = query.setFirstResult(start).setMaxResults(limit).list();
        Page<Object[]> page = new Page<Object[]>();
        page.setTotalCount(total);
        if (result != null && !result.isEmpty())
            page.setResult(result);
        return page;
    }

    public void saveCompanyMapping(CompanyData companyData){
        String sqlCount="select count(1) from t_jiuqi_company_mapping b where b.company1_id=:companyId1 and b.company2_id=:companyId2 and status=1";
        SQLQuery query=createSQLQuery(sqlCount);
        query.setInteger("companyId1",companyData.getCompanyId1());
        query.setInteger("companyId2",companyData.getCompanyId2());
        Integer count = (Integer) query.uniqueResult();
        if(count<=0){
            String sql="insert into t_jiuqi_company_mapping(company1_id,company2_id,code,status) values(:companyId1,:companyId2,:code,1)";
            query=createSQLQuery(sql);
            query.setInteger("companyId1",companyData.getCompanyId1());
            query.setInteger("companyId2",companyData.getCompanyId2());
            query.setString("code",companyData.getCode());
            query.executeUpdate();
        }else{
            String sql="update t_jiuqi_company_mapping set code=:code where company1_id=:companyId1 and company2_id=:companyId2 and status=1";
            query=createSQLQuery(sql);
            query.setInteger("companyId1",companyData.getCompanyId1());
            query.setInteger("companyId2",companyData.getCompanyId2());
            query.setString("code",companyData.getCode());
            query.executeUpdate();
        }
    }
}
